"""
Copyright (c) 2024 [Baijiankang/DUT]
简易火车订票系统

联系信息: [jiankang_bai@163.com]
项目地址: [https://gitee.com/BJKgitee/simple-train-ticket-booking-system]

保留所有权利。
"""

import csv
import mysql.connector
import pandas as pd

def csv_to_sql(filepath):
    con = mysql.connector.connect(
        host='localhost',
        user='root',
        password='<your password>',  # 密码（以实际为准）
        auth_plugin="<mysql_native_password>",  # 认证插件（以实际为准）
        db='trains'  # 数据库（以实际为准）
        )

    cursor = con.cursor()

    drop_table_query = "DROP TABLE IF EXISTS train;"# 删除表的SQL命令
    cursor.execute(drop_table_query)

    cursor.execute("SHOW TABLES")
    tables = cursor.fetchall()
    table_exist = False

    for table in tables:
        if table[0] == 'train':  # 判断表是否存在（以实际为准）
            table_exist = True
            break
    if not table_exist:
        create_table_query = """
        CREATE TABLE train (
            start_s VARCHAR(300),
            end_s VARCHAR(300),
            type VARCHAR(300),
            distance VARCHAR(300),
            time VARCHAR(300),
            stations VARCHAR(3000),
            forward VARCHAR(3000),
            backward VARCHAR(3000)
            )
        """
        cursor.execute(create_table_query)  # 表名（以实际为准）

    with open(filepath, encoding='utf-8') as f:
        reader = csv.reader(f)
        next(reader)  # 跳过表头（以实际为准）
        for row in reader:
            cursor.execute('INSERT INTO train(start_s,end_s,type,distance,time,stations,forward,backward) '
                           'VALUES (%s, %s, %s, %s, %s, %s, %s, %s)', row)  # 表头格式（以实际为准）
        con.commit()
    cursor.close()
    con.close()


def sql_to_csv(filepath):
    con = mysql.connector.connect(
        host='localhost',
        user='root',
        password='<your password>',  # 密码（以实际为准）
        auth_plugin="<mysql_native_password>",  # 认证插件（以实际为准）
        db='trains'  # 数据库（以实际为准）
        )

    cursor = con.cursor()
    cursor.execute('SELECT * FROM train')  # 表名（以实际为准）
    rows = cursor.fetchall()

    with open(filepath, 'w') as f:
        writer = csv.writer(f)
        writer.writerow([i[0] for i in cursor.description])  # 写表头（以实际为准）
        writer.writerows(rows)

    cursor.close()
    con.close()


def getdata(filepath):
    con = mysql.connector.connect(
        host='localhost',
        user='root',
        password='<your password>',  # 密码（以实际为准）
        auth_plugin="<mysql_native_password>",  # 认证插件（以实际为准）
        db='trains'  # 数据库（以实际为准）
        )

    cursor = con.cursor()
    cursor.execute('SELECT * FROM train')  # 表名（以实际为准）
    rows = cursor.fetchall()

    with open(filepath, 'w') as f:
        writer = csv.writer(f)
        writer.writerow([i[0] for i in cursor.description])  # 写表头（以实际为准）
        writer.writerows(rows)

    cursor.close()
    con.close()

#df = pd.read_json('MergedTrainInfo_new.json')
#df.to_csv('data.csv', encoding='utf-8', index=False)
#csv_path1 = r'data.csv'  # 转入csv文件
#csv_to_sql(csv_path1)

#csv_path2 = r'csvData.csv'  # 转入csv文件（以实际为准）
#sql_to_csv(csv_path2)
